package com.asiainfo.dao;

import com.asiainfo.domain.Card;
import com.asiainfo.domain.Member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class OperatorData {
	

	
	/**
	 * 
	 * @param tableName 表明
	 * @param colName 字段名
	 * @return 表中字段当前的最大值
	 * 本函数主要用于查询表中的最大ID
	 */
	public static int getMaxId(String tableName, String colName){
		Connection connection = null;
		int id = 0;
		String sql = "select max(" + colName + ") from "+ tableName + ";";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			connection = JdbcConnection.getConnection();
			pstmt = (PreparedStatement)connection.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int col = rs.getMetaData().getColumnCount();
			
			if (rs.next()){
				for(int i=1; i<=col; i++){
					if (rs.getString(1) == null){
						return 0;
					}
					id = Integer.parseInt(rs.getString(1));
				}
			}
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			JdbcConnection.closeConnection(pstmt, rs, connection);
		}
		return id;
	}
	
	/**
	 * 
	 * @param memberName 成员姓名
	 * @return 成员Id
	 */
	public static int getMemIdByName(String memberName) {
		Connection connection = null;
		int id = 0;
		String sql = "select member_id from member where member_name="+"'"+memberName+"';";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			connection = JdbcConnection.getConnection();
			pstmt = (PreparedStatement)connection.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int colNum = rs.getMetaData().getColumnCount();
			if (colNum>=2){
				System.out.println("存在重名用户，请根据客户ID进行操作");
				return id;
			}else{
				while (rs.next()){
					id = rs.getInt(1);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcConnection.closeConnection(pstmt, rs, connection);
		}
		return id;
	}
	/**
	 * 
	 * @param member 成员对象
	 * @return 是否增加成功 boolean
	 */
	public static boolean addMember(Member member){
		Connection connection = null;
		int influencedColNum = 0;
		String sql = "insert into member values(?,?,?,?)";
		PreparedStatement pstmt = null;
		try{
			connection = JdbcConnection.getConnection();
			pstmt = (PreparedStatement) connection.prepareStatement(sql);
			pstmt.setInt(1, member.getMemberId());
			pstmt.setString(2, member.getMemberName());
			pstmt.setString(3, member.getMemberAddr());
			pstmt.setString(4, member.getMemberWork());
			influencedColNum = pstmt.executeUpdate();
			connection.commit();
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			JdbcConnection.closeConnection(pstmt, null, connection);

		}
		//根据执行sql语句返回的受影响的行数判断是否创建成功
		if (influencedColNum == 1){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 
	 * @param card card 对象
	 * @return 是否增加成功
	 */
	public static boolean addCard(Card card){
		Connection connection = null;
		int influencedColNum = 0;
		String sql = "insert into card (member_id,CARD_TYPE, CARD_ID, CARD_TIMES, CARD_STATE)values(?,?,?,?,?)";
		PreparedStatement pstmt = null;
		try{
			connection = JdbcConnection.getConnection();
			pstmt = (PreparedStatement) connection.prepareStatement(sql);
			pstmt.setInt(1, card.getMemberId());
			pstmt.setString(2, card.getCardType());
			pstmt.setInt(3, card.getCardId());
			pstmt.setInt(4, card.getCardTimes());
			pstmt.setString(5, card.getCardState());
			influencedColNum = pstmt.executeUpdate();
			connection.commit();
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			JdbcConnection.closeConnection(pstmt, null, connection);
		}
		//根据执行sql语句返回的受影响的行数判断是否创建成功
		if (influencedColNum == 1){
			return true;
		}else{
			return false;
		}

	}
}
